package myservice.android.activities;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.nio.channels.FileChannel;

import myservice.android.Global;
import myservice.android.R;
import myservice.android.database.Database;
import myservice.android.database.SourceDatabase;
import myservice.android.utilities.CheckBoxMonitor;
import myservice.android.utilities.EditTextMonitor;
import myservice.android.utilities.SpinnerMonitor;
import android.app.AlertDialog;
import android.app.ProgressDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.net.Uri;
import android.os.AsyncTask;
import android.os.Environment;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.CheckBox;
import android.widget.EditText;
import android.widget.RadioButton;
import android.widget.Spinner;

public class SettingsActivity extends BaseSaveableActivity {
	
	private RadioButton sundayButton,mondayButton;
	private CheckBox showInactivesCheckbox, showVideosCheckbox, showCreditTimeCheckbox, autoIncrementReturnCallsCheckbox, autoBackupCheckbox, roundDownTimerCheckbox;	
	private Spinner dateFormatSpinner;
	private Spinner addressFormatSpinner;
	private Spinner yearRangeSpinner;		
	private EditText smsTemplate;
	private Button exportDataButton, importDataButton, saveButton;	
	
	private static int userId = 0; // ID of the user whose data is being imported
	private static boolean dropData = false; // if true, all current database data (except settings) will be deleted
	private static int localSyncId = 0;
	private static boolean importSuccess = false;
	
	@Override
	protected void initializeLayoutIdAndTitle() {
		layoutId = R.layout.settings;
		activityTitle = Global.res.getString(R.string.title_settings);
	}

	@Override
	protected void retrieveViews() {
        sundayButton = (RadioButton) findViewById(R.id.sundayButton);
        mondayButton = (RadioButton) findViewById(R.id.mondayButton);
        showInactivesCheckbox = (CheckBox) findViewById(R.id.showInactivesCheckbox);
        showCreditTimeCheckbox = (CheckBox) findViewById(R.id.showCreditTimeCheckbox);  
        autoIncrementReturnCallsCheckbox = (CheckBox) findViewById(R.id.incrementReturnCallsCheckbox);
        showVideosCheckbox = (CheckBox) findViewById(R.id.showVideosCheckbox);
        roundDownTimerCheckbox = (CheckBox) findViewById(R.id.roundDownTimerCheckbox);
        autoBackupCheckbox = (CheckBox) findViewById(R.id.autoBackupCheckbox);
        dateFormatSpinner = (Spinner) findViewById(R.id.dateFormatSpinner);
        addressFormatSpinner = (Spinner) findViewById(R.id.addressFormatSpinner); 
        yearRangeSpinner = (Spinner) findViewById(R.id.yearRangeSpinner);         
        smsTemplate = (EditText) findViewById(R.id.smsTemplateText); 
        exportDataButton = (Button) findViewById(R.id.exportDataButton);
        importDataButton = (Button) findViewById(R.id.importDataButton);
        saveButton = (Button) findViewById(R.id.saveButton);
	}

	@Override
	protected void loadActivityParameters() { }

	@Override
	protected void assignListeners() {
		
		sundayButton.setOnCheckedChangeListener(new CheckBoxMonitor(this));
		mondayButton.setOnCheckedChangeListener(new CheckBoxMonitor(this));
		showInactivesCheckbox.setOnCheckedChangeListener(new CheckBoxMonitor(this));
		showCreditTimeCheckbox.setOnCheckedChangeListener(new CheckBoxMonitor(this));
		showVideosCheckbox.setOnCheckedChangeListener(new CheckBoxMonitor(this));
		autoIncrementReturnCallsCheckbox.setOnCheckedChangeListener(new CheckBoxMonitor(this));
		roundDownTimerCheckbox.setOnCheckedChangeListener(new CheckBoxMonitor(this));
		autoBackupCheckbox.setOnCheckedChangeListener(new CheckBoxMonitor(this));
		dateFormatSpinner.setOnItemSelectedListener(new SpinnerMonitor(this)); 
		addressFormatSpinner.setOnItemSelectedListener(new SpinnerMonitor(this));
		yearRangeSpinner.setOnItemSelectedListener(new SpinnerMonitor(this));
		smsTemplate.addTextChangedListener(new EditTextMonitor(this));
		
        importDataButton.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View arg0) {
				
				dropData = false;
				
				if (Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)) {		
					
		        	// Get the previously used import folder
	 				Cursor c = Database.select("SELECT value FROM settings WHERE name='syncimportfolder'");
	 				c.moveToNext();
	 				String path = c.getString(c.getColumnIndex("value"));
	 				if (path.length()<1) {
	 					path = Environment.getExternalStorageDirectory().getPath();
	 					if (Global.folderMyServiceExists)
	 						path = path+"/MyService";	 						
	 				}
	 				c.close();					
					
					Intent intent = new Intent(getBaseContext(), FileDialog.class);					
	                intent.putExtra(FileDialog.START_PATH, path);	                
	                intent.putExtra(FileDialog.SELECTION_MODE, FileDialog.MODE_OPEN);
	                intent.putExtra(FileDialog.FORMAT_FILTER, new String[] { "db" });	                	                
	                startActivityForResult(intent, FileDialog.MODE_OPEN);
				} else {
                	Global.displayToast(SettingsActivity.this, Global.res.getString(R.string.message_sd_inaccessible));										
				}				
			}       
        });
        
        exportDataButton.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View arg0) {
				
				if (Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)) {
					
		        	// Get the previously used export folder
	 				Cursor c = Database.select("SELECT value FROM settings WHERE name='syncexportfolder'");
	 				c.moveToNext();
	 				String path = c.getString(c.getColumnIndex("value"));
	 				if (path.length()<1) {
	 					path = Environment.getExternalStorageDirectory().getPath();
	 					if (Global.folderMyServiceExists)
	 						path = path+"/MyService";
	 				}
	 				c.close();	
	 				
					Intent intent = new Intent(getBaseContext(), FileDialog.class);					
	                intent.putExtra(FileDialog.START_PATH, path);	                
	                intent.putExtra(FileDialog.SELECTION_MODE, FileDialog.MODE_SAVE);
	                intent.putExtra(FileDialog.FORMAT_FILTER, new String[] { "db" });	                	                
	                startActivityForResult(intent, FileDialog.MODE_SAVE);	 				
	                
				} else {
                	Global.displayToast(SettingsActivity.this, Global.res.getString(R.string.message_sd_inaccessible));										
				}				
			}        	
        });
        
        saveButton.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View arg0) {
				save();
				finish();
			}
        });        
        
	}
	
    @Override
    protected void updateUi() {
    	
    	if (Global.firstDayOfWeek==0)
    		sundayButton.setChecked(true);
    	else
    		mondayButton.setChecked(true);
    	
    	showCreditTimeCheckbox.setChecked(Global.trackCreditTime);
    	
    	showInactivesCheckbox.setChecked(Global.showInactives);
    	
    	showVideosCheckbox.setChecked(Global.showVideos);
    	
    	autoIncrementReturnCallsCheckbox.setChecked(Global.autoIncrementReturnCalls); 
    	
    	roundDownTimerCheckbox.setChecked(Global.roundDownTimer); 
    	
    	autoBackupCheckbox.setChecked(Global.autoBackupData); 
    	
    	dateFormatSpinner.setSelection(Global.dateFormat);
    	
    	addressFormatSpinner.setSelection(Global.addressFormat);
    	
    	yearRangeSpinner.setSelection(Global.yearRange);    	
    	
    	smsTemplate.setText(Global.smsTemplate);    	    	
    	
    }
    
    @Override
    protected void save() {

    	if (sundayButton.isChecked())
    		Global.firstDayOfWeek=0;
    	else
    		Global.firstDayOfWeek=1;
    	
    	Global.trackCreditTime=showCreditTimeCheckbox.isChecked();
    	
    	Global.showInactives=showInactivesCheckbox.isChecked();   
    	
    	Global.showVideos=showVideosCheckbox.isChecked();
    	
    	Global.autoIncrementReturnCalls=autoIncrementReturnCallsCheckbox.isChecked();
    	
    	Global.roundDownTimer=roundDownTimerCheckbox.isChecked();
    	
    	Global.autoBackupData=autoBackupCheckbox.isChecked();
    	
    	Global.dateFormat=dateFormatSpinner.getSelectedItemPosition();    	
    	
    	Global.addressFormat=addressFormatSpinner.getSelectedItemPosition();  
    	
    	Global.yearRange=yearRangeSpinner.getSelectedItemPosition();     	
    	
    	Global.smsTemplate=smsTemplate.getText().toString();
    	
    }	
    
    private void performImportingFromDesktop() {
    	
		 AsyncTask<Void, Void, Void> task = new AsyncTask<Void, Void, Void>() {
        	 
			 private ProgressDialog pd;
             
			 @Override
			 protected void onPreExecute() {
				 pd = new ProgressDialog(SettingsActivity.this);
				 pd.setTitle(Global.res.getString(R.string.title_importing));
				 pd.setMessage(Global.res.getString(R.string.message_please_wait));
				 pd.setCancelable(false);
				 pd.setIndeterminate(true);
				 pd.show();
			 }
             
			 @Override
			 protected Void doInBackground(Void... arg0) {
				 try {
					 
					 // Back up the database
					 if (Database.backUpDatabase()==false)
						 return null;		
					 
					 try {
						 
						// Set the new sync ID
						Database.execSQL("UPDATE settings SET value='"+localSyncId+"' WHERE name='syncid'");
						 
						// Import persons
						Log.i(Global.TAG,"Importing persons...");
						if (dropData) {
							 Database.execSQL("DELETE FROM persons");
							 Database.execSQL("DELETE FROM territories");							 
						}
						int newRecords = 0;
						int modifiedRecords = 0;
						int deletedRecords = 0;
						Cursor c1 = SourceDatabase.select("SELECT * FROM persons WHERE userid="+userId);
						while (c1.moveToNext()) {
							long pid = c1.getLong(c1.getColumnIndex("personid"));
							long timestamp = c1.getLong(c1.getColumnIndex("timestamp"));
							Cursor c2 = Database.select("SELECT timestamp FROM persons WHERE personid="+pid);
							if (c2.moveToNext()==false) { // Record does not exist
								Database.execSQL("INSERT INTO persons (personid,name,status,comments,street,houseapartment,townregion,telephone,timestamp) "+
												 "VALUES ("+pid+",'"+c1.getString(c1.getColumnIndex("name"))+"',"+c1.getInt(c1.getColumnIndex("status"))+
												 ",'"+c1.getString(c1.getColumnIndex("description"))+"','"+c1.getString(c1.getColumnIndex("street"))+"','"+
												 c1.getString(c1.getColumnIndex("houseapartment"))+"','"+c1.getString(c1.getColumnIndex("town"))+"','"+
												 c1.getString(c1.getColumnIndex("phone"))+"',"+timestamp+")");
								newRecords++;
							} else { // Record exists
								if (c2.getLong(c2.getColumnIndex("timestamp"))<timestamp) { // existing record is older, must be updated
									Database.execSQL("UPDATE persons SET name='"+c1.getString(c1.getColumnIndex("name"))+"', status="+c1.getInt(c1.getColumnIndex("status"))+
									                 ", comments='"+c1.getString(c1.getColumnIndex("description"))+"', street='"+c1.getString(c1.getColumnIndex("street"))+
									                 "', houseapartment='"+c1.getString(c1.getColumnIndex("houseapartment"))+"', townregion='"+
									                 c1.getString(c1.getColumnIndex("town"))+"', telephone='"+c1.getString(c1.getColumnIndex("phone"))+
									                 "', timestamp="+timestamp+" WHERE personid="+pid);
									if (timestamp!=Global.TIMESTAMP_DELETED)
										modifiedRecords++;
									else {
										deletedRecords++;
										Database.execSQL("UPDATE calls SET timestamp="+Global.TIMESTAMP_DELETED+" WHERE personid="+pid+" AND territoryid=0");
										Database.execSQL("UPDATE calls SET personid=0, timestamp="+Global.getTimeStamp()+" WHERE personid="+pid);
									}
								}								
							}
                            c2.close();
						}
						c1.close();
						Log.i(Global.TAG,"Added "+newRecords+", modified "+modifiedRecords+", deleted "+deletedRecords);
						
						// Import ministry data
						Log.i(Global.TAG,"Importing ministry data...");
						if (dropData)
							 Database.execSQL("DELETE FROM ministry");
						newRecords = 0;
						modifiedRecords = 0;
						deletedRecords = 0;
						c1 = SourceDatabase.select("SELECT * FROM servicerecord WHERE userid="+userId);
						while (c1.moveToNext()) {
							int date = c1.getInt(c1.getColumnIndex("date"));
							int month = c1.getInt(c1.getColumnIndex("month"));
							int year = c1.getInt(c1.getColumnIndex("year"));
							int tracts = 0;
							int videos = 0;
							try { // tracts and videos were not present in older releases
								tracts=c1.getInt(c1.getColumnIndex("tracts"));
								videos=c1.getInt(c1.getColumnIndex("videos"));
							} catch (Exception e) {	}							
							long timestamp = c1.getLong(c1.getColumnIndex("timestamp"));
							Cursor c2 = Database.select("SELECT timestamp FROM ministry WHERE date="+date+" AND month="+month+" AND year="+year);
							if (c2.moveToNext()==false) { // Record does not exist
								Database.execSQL("INSERT INTO ministry (date,month,year,hours,minutes,chours,cminutes,magazines,brochures,books,tracts,videos,returncalls,comments,timestamp) "+
											     "VALUES ("+date+","+month+","+year+","+c1.getInt(c1.getColumnIndex("hours"))+","+c1.getInt(c1.getColumnIndex("minutes"))+
											     ","+c1.getInt(c1.getColumnIndex("chours"))+","+c1.getInt(c1.getColumnIndex("cminutes"))+","+
											     c1.getInt(c1.getColumnIndex("magazines"))+","+c1.getInt(c1.getColumnIndex("brochures"))+","+c1.getInt(c1.getColumnIndex("books"))+
											     ","+tracts+","+videos+","+c1.getInt(c1.getColumnIndex("returnvisits"))+",'"+c1.getString(c1.getColumnIndex("comments"))+"',"+timestamp+")");
								newRecords++;
							} else { // Record exists
								if (c2.getLong(c2.getColumnIndex("timestamp"))<timestamp) { // existing record is older, must be updated
									Database.execSQL("UPDATE ministry SET hours="+c1.getInt(c1.getColumnIndex("hours"))+", minutes="+c1.getInt(c1.getColumnIndex("minutes"))+
											         ", chours="+c1.getInt(c1.getColumnIndex("chours"))+", cminutes="+c1.getInt(c1.getColumnIndex("cminutes"))+", magazines="+
											         c1.getInt(c1.getColumnIndex("magazines"))+", brochures="+c1.getInt(c1.getColumnIndex("brochures"))+", books="+
											         c1.getInt(c1.getColumnIndex("books"))+", tracts="+tracts+", videos="+videos+", returncalls="+c1.getInt(c1.getColumnIndex("returnvisits"))+
											         ", comments='"+c1.getString(c1.getColumnIndex("comments"))+"', timestamp="+timestamp+" WHERE date="+date+" AND month="+month+" AND year="+year);
									if (timestamp!=Global.TIMESTAMP_DELETED)
										modifiedRecords++;
									else
										deletedRecords++;
								}								
							}
                            c2.close();
						}
						c1.close();
						Log.i(Global.TAG,"Added "+newRecords+", modified "+modifiedRecords+", deleted "+deletedRecords);	
						
						// Import calls
						Log.i(Global.TAG,"Importing calls...");
						if (dropData)
							 Database.execSQL("DELETE FROM calls");
						newRecords = 0;
						modifiedRecords = 0;
						deletedRecords = 0;
						c1 = SourceDatabase.select("SELECT * FROM visits WHERE userid="+userId);
						while (c1.moveToNext()) {
							long cid = c1.getLong(c1.getColumnIndex("visitid"));
							long timestamp = c1.getLong(c1.getColumnIndex("timestamp"));
							Cursor c2 = Database.select("SELECT timestamp FROM calls WHERE callid="+cid);
							if (c2.moveToNext()==false) { // Record does not exist
								Database.execSQL("INSERT INTO calls (callid,date,month,year,personid,territoryid,comments,biblestudy,timestamp) "+
												 "VALUES ("+cid+","+c1.getInt(c1.getColumnIndex("date"))+","+c1.getInt(c1.getColumnIndex("month"))+
												 ","+c1.getInt(c1.getColumnIndex("year"))+","+c1.getInt(c1.getColumnIndex("personid"))+",0,'"+c1.getString(c1.getColumnIndex("comments"))+
												 "',"+c1.getInt(c1.getColumnIndex("biblestudy"))+","+timestamp+")");
								newRecords++;
							} else { // Record exists
								if (c2.getLong(c2.getColumnIndex("timestamp"))<timestamp) { // existing record is older, must be updated
									Database.execSQL("UPDATE calls SET date="+c1.getInt(c1.getColumnIndex("date"))+", month="+c1.getInt(c1.getColumnIndex("month"))+
											         ", year="+c1.getInt(c1.getColumnIndex("year"))+", comments='"+c1.getString(c1.getColumnIndex("comments"))+"', biblestudy="+
											         c1.getInt(c1.getColumnIndex("biblestudy"))+", timestamp="+timestamp+" WHERE callid="+cid);
									if (timestamp!=Global.TIMESTAMP_DELETED)
										modifiedRecords++;
									else
										deletedRecords++;
								}								
							}
                            c2.close();
						}
						c1.close();
						Log.i(Global.TAG,"Added "+newRecords+", modified "+modifiedRecords+", deleted "+deletedRecords);	
						
						// Import publications
						Log.i(Global.TAG,"Importing publications...");
						if (dropData)
							 Database.execSQL("DELETE FROM publications");
						newRecords = 0;
						modifiedRecords = 0;
						deletedRecords = 0;
						c1 = SourceDatabase.select("SELECT * FROM publications WHERE userid="+userId);
						while (c1.moveToNext()) {
							long pid = c1.getLong(c1.getColumnIndex("pubid"));
							long timestamp = c1.getLong(c1.getColumnIndex("timestamp"));
							Cursor c2 = Database.select("SELECT timestamp FROM publications WHERE publicationid="+pid);
							if (c2.moveToNext()==false) { // Record does not exist
								Database.execSQL("INSERT INTO publications (publicationid,title,type,timestamp) VALUES ("+pid+",'"+c1.getString(c1.getColumnIndex("title"))+
										         "',"+c1.getInt(c1.getColumnIndex("type"))+","+timestamp+")");
								newRecords++;
							} else { // Record exists
								if (c2.getLong(c2.getColumnIndex("timestamp"))<timestamp) { // existing record is older, must be updated
									Database.execSQL("UPDATE publications SET title='"+c1.getString(c1.getColumnIndex("title"))+"', type="+c1.getInt(c1.getColumnIndex("type"))+
												     ", timestamp="+timestamp+" WHERE publicationid="+pid);
									if (timestamp!=Global.TIMESTAMP_DELETED)
										modifiedRecords++;
									else
										deletedRecords++;
								}								
							}
                            c2.close();
						}
						c1.close();
						Log.i(Global.TAG,"Added "+newRecords+", modified "+modifiedRecords+", deleted "+deletedRecords);	
						
						// Import publication placements
						Log.i(Global.TAG,"Adjusting publication placements...");
						if (dropData)
							 Database.execSQL("DELETE FROM placedpublications");
						newRecords = 0;
						modifiedRecords = 0;
						deletedRecords = 0;
						c1 = SourceDatabase.select("SELECT * FROM personpublications WHERE userid="+userId);
						while (c1.moveToNext()) {
							long pid = c1.getLong(c1.getColumnIndex("personid"));
							long pubid = c1.getLong(c1.getColumnIndex("pubid"));							
							long timestamp = c1.getLong(c1.getColumnIndex("timestamp"));
							Cursor c2 = Database.select("SELECT timestamp FROM placedpublications WHERE publicationid="+pubid+" AND personid="+pid);
							if (c2.moveToNext()==false) { // Record does not exist
								Database.execSQL("INSERT INTO placedpublications (publicationid,personid,placed,timestamp) VALUES ("+pubid+","+pid+","+
							                     c1.getInt(c1.getColumnIndex("placed"))+","+timestamp+")");
								newRecords++;
							} else { // Record exists
								if (c2.getLong(c2.getColumnIndex("timestamp"))<timestamp) { // existing record is older, must be updated
									Database.execSQL("UPDATE placedpublications SET placed="+c1.getInt(c1.getColumnIndex("placed"))+", timestamp="+timestamp+
											         " WHERE publicationid="+pubid+" AND personid="+pid);
									if (timestamp!=Global.TIMESTAMP_DELETED)
										modifiedRecords++;
									else
										deletedRecords++;
								}								
							}
                            c2.close();
						}
						c1.close();
						Log.i(Global.TAG,"Added "+newRecords+", modified "+modifiedRecords+", deleted "+deletedRecords);						
						 
					 } catch (Exception e) {						 
						 SourceDatabase.closeConnection();
						 Database.restoreDatabaseFromBackup(); 						 
						 return null;
					 }
						 
					 SourceDatabase.closeConnection();
					 importSuccess = true;
        		 
				 } catch (Exception e) {
					 Log.e(Global.TAG,e.getMessage());
					 SourceDatabase.closeConnection();
				 }
        	 
				 return null;
			 }
             
			 @Override
			 protected void onPostExecute(Void result) {
				 pd.dismiss();
				 if (importSuccess==true)
					 Global.displayToast(SettingsActivity.this, Global.res.getString(R.string.message_db_importing_success));
				 else
					 Global.displayToast(SettingsActivity.this, Global.res.getString(R.string.message_db_importing_error));					 
			 }
		 };
     
		 task.execute((Void[])null);		 	 
		 
    }
    
    private void performImportingFromMobile() {
    	
		 AsyncTask<Void, Void, Void> task = new AsyncTask<Void, Void, Void>() {
        	 
			 private ProgressDialog pd;
             
			 @Override
			 protected void onPreExecute() {
				 pd = new ProgressDialog(SettingsActivity.this);
				 pd.setTitle(Global.res.getString(R.string.title_importing));
				 pd.setMessage(Global.res.getString(R.string.message_please_wait));
				 pd.setCancelable(false);
				 pd.setIndeterminate(true);
				 pd.show();
			 }
             
			 @Override
			 protected Void doInBackground(Void... arg0) {
				 try {
					 
					 // Back up the database
					 if (Database.backUpDatabase()==false)
						 return null;		
					 
					 try {
						 
						// Set the new sync ID
						Database.execSQL("UPDATE settings SET value='"+localSyncId+"' WHERE name='syncid'");
						 
						// Import persons
						Log.i(Global.TAG,"Importing persons...");
						if (dropData) {
							 Database.execSQL("DELETE FROM persons");							 
						}
						int newRecords = 0;
						int modifiedRecords = 0;
						int deletedRecords = 0;
						Cursor c1 = SourceDatabase.select("SELECT * FROM persons");
						while (c1.moveToNext()) {
							long pid = c1.getLong(c1.getColumnIndex("personid"));
							long timestamp = c1.getLong(c1.getColumnIndex("timestamp"));
							Cursor c2 = Database.select("SELECT timestamp FROM persons WHERE personid="+pid);
							if (c2.moveToNext()==false) { // Record does not exist
								Database.execSQL("INSERT INTO persons (personid,name,status,comments,street,houseapartment,townregion,telephone,timestamp) "+
												 "VALUES ("+pid+",'"+c1.getString(c1.getColumnIndex("name"))+"',"+c1.getInt(c1.getColumnIndex("status"))+
												 ",'"+c1.getString(c1.getColumnIndex("comments"))+"','"+c1.getString(c1.getColumnIndex("street"))+"','"+
												 c1.getString(c1.getColumnIndex("houseapartment"))+"','"+c1.getString(c1.getColumnIndex("townregion"))+"','"+
												 c1.getString(c1.getColumnIndex("telephone"))+"',"+timestamp+")");
								newRecords++;
							} else { // Record exists
								if (c2.getLong(c2.getColumnIndex("timestamp"))<timestamp) { // existing record is older, must be updated
									Database.execSQL("UPDATE persons SET name='"+c1.getString(c1.getColumnIndex("name"))+"', status="+c1.getInt(c1.getColumnIndex("status"))+
									                 ", comments='"+c1.getString(c1.getColumnIndex("comments"))+"', street='"+c1.getString(c1.getColumnIndex("street"))+
									                 "', houseapartment='"+c1.getString(c1.getColumnIndex("houseapartment"))+"', townregion='"+
									                 c1.getString(c1.getColumnIndex("townregion"))+"', telephone='"+c1.getString(c1.getColumnIndex("telephone"))+
									                 "', timestamp="+timestamp+" WHERE personid="+pid);
									if (timestamp!=Global.TIMESTAMP_DELETED)
										modifiedRecords++;
									else {
										deletedRecords++;
										Database.execSQL("UPDATE calls SET timestamp="+Global.TIMESTAMP_DELETED+" WHERE personid="+pid+" AND territoryid=0");
										Database.execSQL("UPDATE calls SET personid=0, timestamp="+Global.getTimeStamp()+" WHERE personid="+pid);
									}
								}								
							}
						}
						c1.close();
						Log.i(Global.TAG,"Added "+newRecords+", modified "+modifiedRecords+", deleted "+deletedRecords);
						
						// Import ministry data
						Log.i(Global.TAG,"Importing ministry data...");
						if (dropData)
							 Database.execSQL("DELETE FROM ministry");
						newRecords = 0;
						modifiedRecords = 0;
						deletedRecords = 0;
						c1 = SourceDatabase.select("SELECT * FROM ministry");
						while (c1.moveToNext()) {
							int date = c1.getInt(c1.getColumnIndex("date"));
							int month = c1.getInt(c1.getColumnIndex("month"));
							int year = c1.getInt(c1.getColumnIndex("year"));
							int tracts = 0;
							int videos = 0;
							try { // tracts and videos were not present in older releases
								tracts=c1.getInt(c1.getColumnIndex("tracts"));
								videos=c1.getInt(c1.getColumnIndex("videos"));
							} catch (Exception e) {	}
							long timestamp = c1.getLong(c1.getColumnIndex("timestamp"));
							Cursor c2 = Database.select("SELECT timestamp FROM ministry WHERE date="+date+" AND month="+month+" AND year="+year);
							if (c2.moveToNext()==false) { // Record does not exist
								Database.execSQL("INSERT INTO ministry (date,month,year,hours,minutes,chours,cminutes,magazines,brochures,books,tracts,videos,returncalls,comments,timestamp) "+
											     "VALUES ("+date+","+month+","+year+","+c1.getInt(c1.getColumnIndex("hours"))+","+c1.getInt(c1.getColumnIndex("minutes"))+
											     ","+c1.getInt(c1.getColumnIndex("chours"))+","+c1.getInt(c1.getColumnIndex("cminutes"))+","+
											     c1.getInt(c1.getColumnIndex("magazines"))+","+c1.getInt(c1.getColumnIndex("brochures"))+","+c1.getInt(c1.getColumnIndex("books"))+
											     ","+tracts+","+videos+","+c1.getInt(c1.getColumnIndex("returncalls"))+",'"+c1.getString(c1.getColumnIndex("comments"))+"',"+timestamp+")");
								newRecords++;
							} else { // Record exists
								if (c2.getLong(c2.getColumnIndex("timestamp"))<timestamp) { // existing record is older, must be updated
									Database.execSQL("UPDATE ministry SET hours="+c1.getInt(c1.getColumnIndex("hours"))+", minutes="+c1.getInt(c1.getColumnIndex("minutes"))+
											         ", chours="+c1.getInt(c1.getColumnIndex("chours"))+", cminutes="+c1.getInt(c1.getColumnIndex("cminutes"))+", magazines="+
											         c1.getInt(c1.getColumnIndex("magazines"))+", brochures="+c1.getInt(c1.getColumnIndex("brochures"))+", books="+
											         c1.getInt(c1.getColumnIndex("books"))+", tracts="+tracts+", videos="+videos+", returncalls="+c1.getInt(c1.getColumnIndex("returncalls"))+
											         ", comments='"+c1.getString(c1.getColumnIndex("comments"))+"', timestamp="+timestamp+" WHERE date="+date+" AND month="+month+" AND year="+year);
									if (timestamp!=Global.TIMESTAMP_DELETED)
										modifiedRecords++;
									else
										deletedRecords++;
								}								
							}
                            c2.close();
						}
						c1.close();
						Log.i(Global.TAG,"Added "+newRecords+", modified "+modifiedRecords+", deleted "+deletedRecords);	
						
						// Import calls
						Log.i(Global.TAG,"Importing calls...");
						if (dropData)
							 Database.execSQL("DELETE FROM calls");
						newRecords = 0;
						modifiedRecords = 0;
						deletedRecords = 0;
						c1 = SourceDatabase.select("SELECT * FROM calls");
						while (c1.moveToNext()) {
							long cid = c1.getLong(c1.getColumnIndex("callid"));
							long timestamp = c1.getLong(c1.getColumnIndex("timestamp"));
							Cursor c2 = Database.select("SELECT timestamp FROM calls WHERE callid="+cid);
							if (c2.moveToNext()==false) { // Record does not exist
								Database.execSQL("INSERT INTO calls (callid,date,month,year,personid,territoryid,comments,biblestudy,timestamp) "+
												 "VALUES ("+cid+","+c1.getInt(c1.getColumnIndex("date"))+","+c1.getInt(c1.getColumnIndex("month"))+
												 ","+c1.getInt(c1.getColumnIndex("year"))+","+c1.getInt(c1.getColumnIndex("personid"))+","+c1.getInt(c1.getColumnIndex("territoryid"))+
												 ",'"+c1.getString(c1.getColumnIndex("comments"))+"',"+c1.getInt(c1.getColumnIndex("biblestudy"))+","+timestamp+")");
								newRecords++;
							} else { // Record exists
								if (c2.getLong(c2.getColumnIndex("timestamp"))<timestamp) { // existing record is older, must be updated
									Database.execSQL("UPDATE calls SET date="+c1.getInt(c1.getColumnIndex("date"))+", month="+c1.getInt(c1.getColumnIndex("month"))+
											         ", year="+c1.getInt(c1.getColumnIndex("year"))+", personid="+c1.getInt(c1.getColumnIndex("personid"))+", territoryid="+
											         c1.getInt(c1.getColumnIndex("territoryid"))+", comments='"+c1.getString(c1.getColumnIndex("comments"))+"', biblestudy="+
											         c1.getInt(c1.getColumnIndex("biblestudy"))+", timestamp="+timestamp+" WHERE callid="+cid);
									if (timestamp!=Global.TIMESTAMP_DELETED)
										modifiedRecords++;
									else
										deletedRecords++;
								}								
							}
                            c2.close();
						}
						c1.close();
						Log.i(Global.TAG,"Added "+newRecords+", modified "+modifiedRecords+", deleted "+deletedRecords);	
						
						// Import publications
						Log.i(Global.TAG,"Importing publications...");
						if (dropData)
							 Database.execSQL("DELETE FROM publications");
						newRecords = 0;
						modifiedRecords = 0;
						deletedRecords = 0;
						c1 = SourceDatabase.select("SELECT * FROM publications");
						while (c1.moveToNext()) {
							long pid = c1.getLong(c1.getColumnIndex("publicationid"));
							long timestamp = c1.getLong(c1.getColumnIndex("timestamp"));
							Cursor c2 = Database.select("SELECT timestamp FROM publications WHERE publicationid="+pid);
							if (c2.moveToNext()==false) { // Record does not exist
								Database.execSQL("INSERT INTO publications (publicationid,title,type,timestamp) VALUES ("+pid+",'"+c1.getString(c1.getColumnIndex("title"))+
										         "',"+c1.getInt(c1.getColumnIndex("type"))+","+timestamp+")");
								newRecords++;
							} else { // Record exists
								if (c2.getLong(c2.getColumnIndex("timestamp"))<timestamp) { // existing record is older, must be updated
									Database.execSQL("UPDATE publications SET title='"+c1.getString(c1.getColumnIndex("title"))+"', type="+c1.getInt(c1.getColumnIndex("type"))+
												     ", timestamp="+timestamp+" WHERE publicationid="+pid);
									if (timestamp!=Global.TIMESTAMP_DELETED)
										modifiedRecords++;
									else
										deletedRecords++;
								}								
							}
                            c2.close();
						}
						c1.close();
						Log.i(Global.TAG,"Added "+newRecords+", modified "+modifiedRecords+", deleted "+deletedRecords);	
						
						// Import publication placements
						Log.i(Global.TAG,"Adjusting publication placements...");
						if (dropData)
							 Database.execSQL("DELETE FROM placedpublications");
						newRecords = 0;
						modifiedRecords = 0;
						deletedRecords = 0;
						c1 = SourceDatabase.select("SELECT * FROM placedpublications");
						while (c1.moveToNext()) {
							long pid = c1.getLong(c1.getColumnIndex("personid"));
							long pubid = c1.getLong(c1.getColumnIndex("publicationid"));							
							long timestamp = c1.getLong(c1.getColumnIndex("timestamp"));
							Cursor c2 = Database.select("SELECT timestamp FROM placedpublications WHERE publicationid="+pubid+" AND personid="+pid);
							if (c2.moveToNext()==false) { // Record does not exist
								Database.execSQL("INSERT INTO placedpublications (publicationid,personid,placed,timestamp) VALUES ("+pubid+","+pid+","+
							                     c1.getInt(c1.getColumnIndex("placed"))+","+timestamp+")");
								newRecords++;
							} else { // Record exists
								if (c2.getLong(c2.getColumnIndex("timestamp"))<timestamp) { // existing record is older, must be updated
									Database.execSQL("UPDATE placedpublications SET placed="+c1.getInt(c1.getColumnIndex("placed"))+", timestamp="+timestamp+
											         " WHERE publicationid="+pubid+" AND personid="+pid);
									if (timestamp!=Global.TIMESTAMP_DELETED)
										modifiedRecords++;
									else
										deletedRecords++;
								}								
							}
                            c2.close();
						}
						c1.close();
						Log.i(Global.TAG,"Added "+newRecords+", modified "+modifiedRecords+", deleted "+deletedRecords);
						
						// Import territories
						Log.i(Global.TAG,"Importing territories...");
						if (dropData)
							 Database.execSQL("DELETE FROM territories");
						newRecords = 0;
						modifiedRecords = 0;
						deletedRecords = 0;
						c1 = SourceDatabase.select("SELECT * FROM territories");
						while (c1.moveToNext()) {
							long tid = c1.getLong(c1.getColumnIndex("territoryid"));
							long timestamp = c1.getLong(c1.getColumnIndex("timestamp"));
							Cursor c2 = Database.select("SELECT timestamp FROM territories WHERE territoryid="+tid);
							if (c2.moveToNext()==false) { // Record does not exist
								Database.execSQL("INSERT INTO territories (territoryid,type,parent,name,comments,completed,timestamp) VALUES ("+tid+","+c1.getInt(c1.getColumnIndex("type"))+
												 ","+c1.getInt(c1.getColumnIndex("parent"))+",'"+c1.getString(c1.getColumnIndex("name"))+"','"+c1.getString(c1.getColumnIndex("comments"))+
												 "',"+c1.getInt(c1.getColumnIndex("completed"))+","+timestamp+")");
								newRecords++;
							} else { // Record exists
								if (c2.getLong(c2.getColumnIndex("timestamp"))<timestamp) { // existing record is older, must be updated
									Database.execSQL("UPDATE territories SET type="+c1.getInt(c1.getColumnIndex("type"))+", parent="+c1.getInt(c1.getColumnIndex("parent"))+
											         ", name='"+c1.getString(c1.getColumnIndex("name"))+"', comments='"+c1.getString(c1.getColumnIndex("comments"))+"', completed="+
											         c1.getInt(c1.getColumnIndex("completed"))+", timestamp="+timestamp+" WHERE territoryid="+tid);
									if (timestamp!=Global.TIMESTAMP_DELETED)
										modifiedRecords++;
									else
										deletedRecords++;
										Database.execSQL("UPDATE calls SET timestamp="+Global.TIMESTAMP_DELETED+" WHERE territoryid="+tid+" AND personid=0");
										Database.execSQL("UPDATE calls SET territory=0, timestamp="+Global.getTimeStamp()+" WHERE territoryid="+tid);
								}								
							}
                            c2.close();
						}
						c1.close();
						Log.i(Global.TAG,"Added "+newRecords+", modified "+modifiedRecords+", deleted "+deletedRecords);						
						 
					 } catch (Exception e) {						 
						 SourceDatabase.closeConnection();
						 Database.restoreDatabaseFromBackup(); 						 
						 return null;
					 }
						 
					 SourceDatabase.closeConnection();
					 importSuccess = true;
        		 
				 } catch (Exception e) {
					 Log.e(Global.TAG,e.getMessage());
					 SourceDatabase.closeConnection();
				 }
        	 
				 return null;
			 }
             
			 @Override
			 protected void onPostExecute(Void result) {
				 pd.dismiss();
				 if (importSuccess==true)
					 Global.displayToast(SettingsActivity.this, Global.res.getString(R.string.message_db_importing_success));
				 else
					 Global.displayToast(SettingsActivity.this, Global.res.getString(R.string.message_db_importing_error));					 
			 }
		 };
     
		 task.execute((Void[])null);		 	 
		 
    }    
    
    protected void onActivityResult(int requestCode, int resultCode, Intent data) {
    	
    	if (resultCode == RESULT_OK) {   
    		
    		final String resultPath = data.getStringExtra(FileDialog.RESULT_PATH);         
    		
	        if (requestCode == FileDialog.MODE_OPEN) { // Handle database importing	   
	        	
        		// Get the local sync ID
        		Cursor c = Database.select("SELECT value FROM settings WHERE name='syncid'");
        		c.moveToNext();
        		localSyncId = c.getInt(c.getColumnIndex("value"));
    			c.close();
	        	 
	        	// Preimporting tasks
        		if (SourceDatabase.openConnection(resultPath)==false) {
        			Global.displayToast(SettingsActivity.this, Global.res.getString(R.string.message_cannot_open_db));
        			return;
        		}
        			 
        		// Saving the path to the database
        		Database.execSQL("UPDATE settings SET value='"+resultPath.replace("/MyService_desktop.db", "")+"' WHERE name='syncimportfolder'");
        			 
        		// Check whether the database comes from mobile app or desktop app
        		Log.i(Global.TAG,"Checking the database type...");
        		c = SourceDatabase.select("SELECT COUNT(*) AS c FROM sqlite_master WHERE type='table' AND name='servicerecord'");
        		c.moveToNext();
        		int i = c.getInt(c.getColumnIndex("c"));
        		c.close();
        		
        		if (i==1) {
        			
        			// IMPORTING DATABASE FROM THE DESKTOP APP
        			
            		// Check if the database is of correct version
            		Log.i(Global.TAG,"Checking the database version...");
            		c = SourceDatabase.select("SELECT value FROM system WHERE item='DBVersion'");
            		c.moveToNext();
            		if (!c.getString(c.getColumnIndex("value")).equals("4.0") && !c.getString(c.getColumnIndex("value")).equals("4.1")) {	                				 
            			Log.e(Global.TAG,"Database of that version is not supported!");
            			Global.displayToast(SettingsActivity.this, Global.res.getString(R.string.message_db_importing_error));        			
                    	SourceDatabase.closeConnection();	
            			return;
            		}
            		c.close();
            			 
            		// Get the synchronized user ID
            		Log.i(Global.TAG,"Looking for the syncrhonized user ID...");
            		c = SourceDatabase.select("SELECT value FROM system WHERE item='exportinguser'");
            		c.moveToNext();
            		userId = c.getInt(c.getColumnIndex("value"));
            		c.close();            			 
        			 
        			// Compare the sync IDs
        			Log.i(Global.TAG,"Comparing synchronization IDs...");	                			 
        			int remoteSyncId = 0;
        			c = SourceDatabase.select("SELECT syncid FROM users WHERE userid="+userId);
        			c.moveToNext();
        			remoteSyncId = c.getInt(c.getColumnIndex("syncid"));
        			c.close();
        			if (localSyncId!=remoteSyncId) {    	
        				localSyncId = remoteSyncId;
        				AlertDialog.Builder alertDialogBuilder = new AlertDialog.Builder(SettingsActivity.this);
    					alertDialogBuilder.setTitle(Global.res.getText(R.string.title_warning));				
    					alertDialogBuilder
    						.setMessage(Global.res.getText(R.string.message_syncid_mismatch))
    						.setPositiveButton(Global.res.getText(R.string.button_yes),new DialogInterface.OnClickListener() {
    							public void onClick(DialogInterface dialog, int id) {
    								dropData = true;								
    								performImportingFromDesktop();
    							}
    						  })
    						.setNegativeButton(Global.res.getText(R.string.button_no),new DialogInterface.OnClickListener() {
    							public void onClick(DialogInterface dialog, int id) {
    			    				Log.i(Global.TAG,"Data importing cancelled!");
    			            		SourceDatabase.closeConnection();	
    			            		dialog.cancel();
    							}
    						});
    			 
    					AlertDialog alertDialog = alertDialogBuilder.create();
    					alertDialog.show();	
        			 } else {
        				performImportingFromDesktop(); 
        			 }   
        			
        		} else {
        			Cursor cc = SourceDatabase.select("SELECT COUNT(*) AS c FROM sqlite_master WHERE type='table' AND name='ministry'");
            		cc.moveToNext();
            		i = cc.getInt(c.getColumnIndex("c"));
            		cc.close();
            		if (i == 1) {
            			
            			// IMPORTING DATABASE FROM THE MOBILE APP            			
            			
                		// Check if the database is of correct version
                		Log.i(Global.TAG,"Checking the database version...");
                		c = SourceDatabase.select("SELECT value FROM settings WHERE name='databaseversion'");
                		c.moveToNext();
                		int version = Integer.parseInt(c.getString(c.getColumnIndex("value")));
                		if (version>2) {	                				 
                			Log.e(Global.TAG,"Database of that version is not supported!");
                			Global.displayToast(SettingsActivity.this, Global.res.getString(R.string.message_db_importing_error));        			
                        	SourceDatabase.closeConnection();	
                			return;
                		}
                		c.close();
                			 
                		// Get the remote sync ID
                		Log.i(Global.TAG,"Looking for the synchronization ID...");
                		c = SourceDatabase.select("SELECT value FROM settings WHERE name='syncid'");
                		c.moveToNext();
                		int remoteSyncId = c.getInt(c.getColumnIndex("value"));
                		c.close();	                			                 			 
            			 
            			// Compare the sync IDs
            			if (localSyncId!=remoteSyncId) {    	
            				localSyncId = remoteSyncId;
            				AlertDialog.Builder alertDialogBuilder = new AlertDialog.Builder(SettingsActivity.this);
        					alertDialogBuilder.setTitle(Global.res.getText(R.string.title_warning));				
        					alertDialogBuilder
        						.setMessage(Global.res.getText(R.string.message_syncid_mismatch))
        						.setPositiveButton(Global.res.getText(R.string.button_yes),new DialogInterface.OnClickListener() {
        							public void onClick(DialogInterface dialog, int id) {
        								dropData = true;								
        								performImportingFromMobile();
        							}
        						  })
        						.setNegativeButton(Global.res.getText(R.string.button_no),new DialogInterface.OnClickListener() {
        							public void onClick(DialogInterface dialog, int id) {
        			    				Log.i(Global.TAG,"Data importing cancelled!");
        			            		SourceDatabase.closeConnection();	
        			            		dialog.cancel();
        							}
        						});
        			 
        					AlertDialog alertDialog = alertDialogBuilder.create();
        					alertDialog.show();	
            			 } else {
            				performImportingFromMobile(); 
            			 }              			
            			
            		} else { // Unknown database file provided
            			Log.e(Global.TAG,"The database is not valid!");
            			Global.displayToast(SettingsActivity.this, Global.res.getString(R.string.message_db_importing_error));
                    	SourceDatabase.closeConnection();	
            			return;
            		}
        		}        			      	         		
	        	 
	        } else if (requestCode == FileDialog.MODE_SAVE) { // Handle database exporting
	        	                 	               
            	Database.execSQL("UPDATE settings SET value='"+resultPath+"' WHERE name='syncexportfolder'");
				
            	Database.closeConnection();
				
				try {
                    // Where the copy of the database will be placed on the external storage
                	String copiedDBPath = resultPath+"//MyService_android.db";
                    File dataDir = Environment.getDataDirectory();

                	// Where the database is stored in the phone memory
                    String originalDBPath = "//data//myservice.android//databases//"+Global.databaseName;	                                
                        
                    File originalDB = new File(dataDir, originalDBPath);
                    File copiedDB = new File(copiedDBPath);

                    if (originalDB.exists()) {
                        FileInputStream fileInputStream = new FileInputStream(originalDB);
						FileChannel src = fileInputStream.getChannel();
                        FileOutputStream fileOutputStream = new FileOutputStream(copiedDB);
						FileChannel dst = fileOutputStream.getChannel();
                        dst.transferFrom(src, 0, src.size());                                                        
                        src.close();
                        dst.close();
                        fileInputStream.close();
                        fileOutputStream.close();                                                         
                            
                        // Scan the file so that Android knows about it
                        Intent intent = new Intent(Intent.ACTION_MEDIA_SCANNER_SCAN_FILE);                            		
                        intent.setData(Uri.fromFile(copiedDB));                            		
                        sendBroadcast(intent);                                                                               
                            
                        Global.displayToast(SettingsActivity.this, Global.res.getString(R.string.message_data_exported));
                    } else {
                    	Log.e(Global.TAG, "Database not found!");
                    	Global.displayToast(SettingsActivity.this, "Database not found!");
                    }	                            
                } catch (Exception e) {
                	Log.e(Global.TAG, e.getMessage());
                	Global.displayToast(SettingsActivity.this, e.getMessage());
                }	        				
				
				Database.openConnection();
	         }
    	}
    }          
    
}